<?php
$seitcm_form = isset($seitcm_form) ? $seitcm_form : seitcm_get_current_date(0,-6,0);
$seitcm_to = isset($seitcm_to) ? $seitcm_to : seitcm_get_current_date(0,0,0);
global $wpdb;
$arr_result = array();
$seitcm_mid = $seitcm_form;
while (seitcm_compare_date($seitcm_to,$seitcm_mid))
{
	$seitcm_mid = seitcm_plus_date($seitcm_mid,0,1,0);
	if(seitcm_compare_date(seitcm_plus_date($seitcm_mid,0,0,0),$seitcm_to))
	{
		$sql = $wpdb->prepare('SELECT COUNT(CASE WHEN `meta_value` = 1 THEN 1 ELSE NULL END) AS `new`,
								COUNT(CASE WHEN `meta_value` = 2 THEN 1 ELSE NULL END) AS `old`,
								COUNT(meta_value) AS `total` FROM wp_usermeta
								WHERE `meta_key` = \'learningstatus\'
								AND `user_id` IN
									(SELECT * FROM (SELECT studentid FROM ' . SEITCM_TABLE_REGISTER . '
														WHERE `time` > %s and `time` < %s ORDER BY `time` DESC) AS A
											GROUP BY studentid)',seitcm_create_date_for_mysql(seitcm_plus_date($seitcm_mid,0,-1,0),false),seitcm_create_date_for_mysql($seitcm_to,true));
		
		array_push($arr_result,array('from'=>seitcm_plus_date($seitcm_mid,0,-1,0),'to'=>$seitcm_to,'result'=>$wpdb->get_row($sql)));
	}
	else {
		$sql = $wpdb->prepare('SELECT COUNT(CASE WHEN `meta_value` = 1 THEN 1 ELSE NULL END) AS `new`,
								COUNT(CASE WHEN `meta_value` = 2 THEN 1 ELSE NULL END) AS `old`,
								COUNT(meta_value) AS `total` FROM wp_usermeta
								WHERE `meta_key` = \'learningstatus\'
								AND `user_id` IN
									(SELECT * FROM (SELECT studentid FROM ' . SEITCM_TABLE_REGISTER . '
														WHERE `time` > %s and `time` < %s ORDER BY `time` DESC) AS A
											GROUP BY studentid)',seitcm_create_date_for_mysql(seitcm_plus_date($seitcm_mid,0,-1,0),false),seitcm_create_date_for_mysql($seitcm_mid,true));

		array_push($arr_result,array('from'=>seitcm_plus_date($seitcm_mid,0,-1,0),'to'=>$seitcm_mid,'result'=>$wpdb->get_row($sql)));
	}
}
?>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load('visualization', '1', {packages: ['corechart']});
</script>
<script type="text/javascript">
  function drawVisualization() {
    // Some raw data (not necessarily accurate)
    var data = google.visualization.arrayToDataTable([
    	['Time', 'New', 'Old', 'Total'],
    	<?php 
    	foreach ($arr_result as $charts)
		{
      	echo '["'.$charts['to'].'",'.$charts['result']->new.','.$charts['result']->old.','.$charts['result']->total.'],';
		}
    	?>
    ]);

    var options = {
      title : '',
      vAxis: {title: "Amount register"},
      hAxis: {title: "Time"},
      seriesType: "bars",
      series: {2: {type: "line"}}
    };

    var chart = new google.visualization.ComboChart(document.getElementById('seitcm_register_statistics'));
    chart.draw(data, options);
  }
  google.setOnLoadCallback(drawVisualization);
</script>
<div id="seitcm_register_statistics" style="min-height:300px"></div>